﻿// See https://ausing System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;

namespace Homework_8
{
    class Program
    {
        static void Main(string[] args)
        {
            var database = new StudentManagementSystemDatabase();
            database.CreateDatabase();

            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("请选择一个选项：");
                Console.WriteLine("1. 学校");
                Console.WriteLine("2. 班级");
                Console.WriteLine("3. 学生");
                Console.WriteLine("4. 日志");
                Console.WriteLine("0. 退出");
                Console.Write("选项：");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        SchoolsMenu(database);
                        break;
                    case "2":
                        ClassesMenu(database);
                        break;
                    case "3":
                        StudentsMenu(database);
                        break;
                    case "4":
                        LogsMenu(database);
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("无效的选项。");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void SchoolsMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("学校菜单");
                Console.WriteLine("1. 列出学校");
                Console.WriteLine("2. 添加学校");
                Console.WriteLine("3. 更新学校");
                Console.WriteLine("4. 删除学校");
                Console.WriteLine("0. 返回");
                Console.Write("选项：");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        var schools = database.GetSchools();
                        Console.WriteLine("学校列表：");
                        foreach (var school1 in schools)
                        {
                            Console.WriteLine($"学校编号：{school1.SchoolId}，名称：{school1.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("学校名称：");
                        string name = Console.ReadLine();
                        var school = new School { Name = name };
                        database.AddSchool(school);
                        Console.WriteLine("学校已添加。");
                        database.LogOperation($"添加学校 {school.Name}。");
                        break;
                    case "3":
                        Console.Write("学校编号：");
                        int schoolId = int.Parse(Console.ReadLine());
                        Console.Write("新学校名称：");
                        name = Console.ReadLine();
                        school = new School { SchoolId = schoolId, Name = name };
                        database.UpdateSchool(school);
                        Console.WriteLine("学校已更新。");
                        database.LogOperation($"更新学校 {school.SchoolId}。");
                        break;
                    case "4":
                        Console.Write("学校编号：");
                        schoolId = int.Parse(Console.ReadLine());
                        database.DeleteSchool(schoolId);
                        Console.WriteLine("学校已删除。");
                        database.LogOperation($"删除学校 {schoolId}。");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("无效的选项。");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void ClassesMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("班级菜单");
                Console.WriteLine("1. 按学校编号列出班级");
                Console.WriteLine("2. 添加班级");
                Console.WriteLine("3. 更新班级");
                Console.WriteLine("4. 删除班级");
                Console.WriteLine("0. 返回");
                Console.Write("选项：");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        Console.Write("学校编号：");
                        int schoolId = int.Parse(Console.ReadLine());
                        var classes = database.GetClassesBySchoolId(schoolId);
                        Console.WriteLine($"学校编号为 {schoolId} 的班级列表：");
                        foreach (var classObj1 in classes)
                        {
                            Console.WriteLine($"班级编号：{classObj1.ClassId}，名称：{classObj1.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("班级名称：");
                        string name = Console.ReadLine();
                        Console.Write("学校编号：");
                        schoolId = int.Parse(Console.ReadLine());
                        var classObj = new Class { Name = name, SchoolId = schoolId };
                        database.AddClass(classObj);
                        Console.WriteLine("班级已添加。");
                        database.LogOperation($"添加班级 {classObj.Name}。");
                        break;
                    case "3":
                        Console.Write("班级编号：");
                        int classId = int.Parse(Console.ReadLine());
                        Console.Write("新班级名称：");
                        name = Console.ReadLine();
                        classObj = new Class { ClassId = classId, Name = name };
                        database.UpdateClass(classObj);
                        Console.WriteLine("班级已更新。");
                        database.LogOperation($"更新班级 {classObj.ClassId}。");
                        break;
                    case "4":
                        Console.Write("班级编号：");
                        classId = int.Parse(Console.ReadLine());
                        database.DeleteClass(classId);
                        Console.WriteLine("班级已删除。");
                        database.LogOperation($"删除班级 {classId}。");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("无效的选项。");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void StudentsMenu(StudentManagementSystemDatabase database)
        {
            bool exit = false;
            while (!exit)
            {
                Console.WriteLine("学生菜单");
                Console.WriteLine("1. 按班级编号列出学生");
                Console.WriteLine("2. 添加学生");
                Console.WriteLine("3. 更新学生");
                Console.WriteLine("4. 删除学生");
                Console.WriteLine("0. 返回");
                Console.Write("选项：");

                string option = Console.ReadLine();
                switch (option)
                {
                    case "1":
                        Console.Write("班级编号：");
                        int classId = int.Parse(Console.ReadLine());
                        var students = database.GetStudentsByClassId(classId);
                        Console.WriteLine($"班级编号为 {classId} 的学生列表：");
                        foreach (var student1 in students)
                        {
                            Console.WriteLine($"学生编号：{student1.StudentId}，姓名：{student1.Name}");
                        }
                        break;
                    case "2":
                        Console.Write("学生姓名：");
                        string name = Console.ReadLine();
                        Console.Write("班级编号：");
                        classId = int.Parse(Console.ReadLine());
                        var student = new Student { Name = name, ClassId = classId };
                        database.AddStudent(student);
                        Console.WriteLine("学生已添加。");
                        database.LogOperation($"添加学生 {student.Name}。");
                        break;
                    case "3":
                        Console.Write("学生编号：");
                        int studentId = int.Parse(Console.ReadLine());
                        Console.Write("新学生姓名：");
                        name = Console.ReadLine();
                        student = new Student { StudentId = studentId, Name = name };
                        database.UpdateStudent(student);
                        Console.WriteLine("学生已更新。");
                        database.LogOperation($"更新学生 {student.StudentId}。");
                        break;
                    case "4":
                        Console.Write("学生编号：");
                        studentId = int.Parse(Console.ReadLine());
                        database.DeleteStudent(studentId);
                        Console.WriteLine("学生已删除。");
                        database.LogOperation($"删除学生 {studentId}。");
                        break;
                    case "0":
                        exit = true;
                        break;
                    default:
                        Console.WriteLine("无效的选项。");
                        break;
                }

                Console.WriteLine();
            }
        }

        static void LogsMenu(StudentManagementSystemDatabase database)
        {
            Console.WriteLine("操作日志");
            var logs = database.GetOperationLogs();
            foreach (var log in logs)
            {
                Console.WriteLine($"时间：{log.Timestamp}，操作：{log.Operation}");
            }
        }
    }

    class School
    {
        public int SchoolId { get; set; }
        public string Name { get; set; }
    }

    class Class
    {
        public int ClassId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
    }

    class Student
    {
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int ClassId { get; set; }
    }

    class OperationLog
    {
        public int LogId { get; set; }
        public DateTime Timestamp { get; set; }
        public string Operation { get; set; }
    }

    class StudentManagementSystemDatabase
    {
        private const string DatabaseFile = "student_management.db";

        public void CreateDatabase()
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "CREATE TABLE IF NOT EXISTS Schools (SchoolId INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)";
                command.ExecuteNonQuery();

                command.CommandText = "CREATE TABLE IF NOT EXISTS Classes (ClassId INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, SchoolId INTEGER, FOREIGN KEY(SchoolId) REFERENCES Schools(SchoolId))";
                command.ExecuteNonQuery();

                command.CommandText = "CREATE TABLE IF NOT EXISTS Students (StudentId INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, ClassId INTEGER, FOREIGN KEY(ClassId) REFERENCES Classes(ClassId))";
                command.ExecuteNonQuery();

                command.CommandText = "CREATE TABLE IF NOT EXISTS OperationLogs (LogId INTEGER PRIMARY KEY AUTOINCREMENT, Timestamp TEXT, Operation TEXT)";
                command.ExecuteNonQuery();
            }
        }

        public List<School> GetSchools()
        {
            var schools = new List<School>();

            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "SELECT * FROM Schools";
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var school = new School
                    {
                        SchoolId = reader.GetInt32(0),
                        Name = reader.GetString(1)
                    };

                    schools.Add(school);
                }
            }

            return schools;
        }

        public void AddSchool(School school)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "INSERT INTO Schools (Name) VALUES (@Name)";
                command.Parameters.AddWithValue("@Name", school.Name);
                command.ExecuteNonQuery();
            }
        }

        public void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "UPDATE Schools SET Name = @Name WHERE SchoolId = @SchoolId";
                command.Parameters.AddWithValue("@Name", school.Name);
                command.Parameters.AddWithValue("@SchoolId", school.SchoolId);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "DELETE FROM Schools WHERE SchoolId = @SchoolId";
                command.Parameters.AddWithValue("@SchoolId", schoolId);
                command.ExecuteNonQuery();
            }
        }

        public List<Class> GetClassesBySchoolId(int schoolId)
        {
            var classes = new List<Class>();

            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "SELECT * FROM Classes WHERE SchoolId = @SchoolId";
                command.Parameters.AddWithValue("@SchoolId", schoolId);
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var classObj = new Class
                    {
                        ClassId = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        SchoolId = reader.GetInt32(2)
                    };

                    classes.Add(classObj);
                }
            }

            return classes;
        }

        public void AddClass(Class classObj)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "INSERT INTO Classes (Name, SchoolId) VALUES (@Name, @SchoolId)";
                command.Parameters.AddWithValue("@Name", classObj.Name);
                command.Parameters.AddWithValue("@SchoolId", classObj.SchoolId);
                command.ExecuteNonQuery();
            }
        }

        public void UpdateClass(Class classObj)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "UPDATE Classes SET Name = @Name WHERE ClassId = @ClassId";
                command.Parameters.AddWithValue("@Name", classObj.Name);
                command.Parameters.AddWithValue("@ClassId", classObj.ClassId);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteClass(int classId)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "DELETE FROM Classes WHERE ClassId = @ClassId";
                command.Parameters.AddWithValue("@ClassId", classId);
                command.ExecuteNonQuery();
            }
        }

        public List<Student> GetStudentsByClassId(int classId)
        {
            var students = new List<Student>();

            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "SELECT * FROM Students WHERE ClassId = @ClassId";
                command.Parameters.AddWithValue("@ClassId", classId);
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var student = new Student
                    {
                        StudentId = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        ClassId = reader.GetInt32(2)
                    };

                    students.Add(student);
                }
            }

            return students;
        }

        public void AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "INSERT INTO Students (Name, ClassId) VALUES (@Name, @ClassId)";
                command.Parameters.AddWithValue("@Name", student.Name);
                command.Parameters.AddWithValue("@ClassId", student.ClassId);
                command.ExecuteNonQuery();
            }
        }

        public void UpdateStudent(Student student)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "UPDATE Students SET Name = @Name WHERE StudentId = @StudentId";
                command.Parameters.AddWithValue("@Name", student.Name);
                command.Parameters.AddWithValue("@StudentId", student.StudentId);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteStudent(int studentId)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "DELETE FROM Students WHERE StudentId = @StudentId";
                command.Parameters.AddWithValue("@StudentId", studentId);
                command.ExecuteNonQuery();
            }
        }

        public List<OperationLog> GetOperationLogs()
        {
            var logs = new List<OperationLog>();

            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "SELECT * FROM OperationLogs";
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    var log = new OperationLog
                    {
                        LogId = reader.GetInt32(0),
                        Timestamp = DateTime.Parse(reader.GetString(1)),
                        Operation = reader.GetString(2)
                    };

                    logs.Add(log);
                }
            }

            return logs;
        }

        public void LogOperation(string operation)
        {
            using (var connection = new SQLiteConnection($"Data Source={DatabaseFile};Version=3;"))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = "INSERT INTO OperationLogs (Timestamp, Operation) VALUES (@Timestamp, @Operation)";
                command.Parameters.AddWithValue("@Timestamp", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                command.Parameters.AddWithValue("@Operation", operation);
                command.ExecuteNonQuery();
            }
        }
    }
}
